﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using IHISCETeim.BLL;

using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using IHISCETeim.Library;

namespace IHISCETeim.Web.Export.FTradeModel
{
    public partial class FTradeExportReport1 : ExportPageBase
    {
        protected override string xlsTemplateFileName
        {
            get
            {
                return "应收帐款_风险部应收帐款汇总.xls";
            }
        }
        protected override string SheetName
        {
            get
            {
                return "应收帐款汇总";
            }

        }

        #region 属性 (查询条件)
        /// <summary>
        /// 是否从报表8分期汇总过来,如果是1,刚只显示分期相关数据
        /// </summary>
        protected int IsShowFenQi
        {
            get
            {
                return GetRequestValue<int>("IsShowFenQi", 0);
            }
        }
        //收款条件
        private string _XiaoShouLeiXing = "";
        protected string XiaoShouLeiXing
        {
            get
            {
                if (_XiaoShouLeiXing == "")
                    _XiaoShouLeiXing = GetRequestValue<string>("XiaoShouLeiXing", "");
                return _XiaoShouLeiXing;
            }
            set
            {
                _XiaoShouLeiXing = value;
            }
        }

        protected string JingXiaoShang
        {
            get
            {
                return GetRequestValue<string>("JingXiaoShang", "");

            }
        }
        protected string HeTongHao
        {
            get
            {
                return GetRequestValue<string>("HeTongHao", "");

            }
        }

        protected string JiHao
        {
            get
            {
                return GetRequestValue<string>("JiHao", "");

            }
        }

        protected string JiQiState
        {
            get
            {
                return GetRequestValue<string>("JiQiState", "-");
            }
        }

        protected string XiaoShouShiJianFrom
        {
            get
            {
                return GetRequestValue<string>("XiaoShouShiJianFrom", "");

            }
        }
        protected string XiaoShouShiJianTo
        {
            get
            {
                if (string.IsNullOrEmpty(XiaoShouShiJianFrom))
                {
                    return "";
                }
                else
                {
                    string todate = GetRequestValue<string>("XiaoShouShiJianTo", "");
                    if (string.IsNullOrEmpty(todate))
                        return Convert.ToDateTime(XiaoShouShiJianFrom).AddDays(1).ToShortDateString();
                    else
                        return todate;
                }

            }
        }

        #endregion

        protected string GetSQLWhere()
        {
            string wheresql = " WeiShouKuanXiang>0 ";
            string sqlcommonFiled = "";
            if (!string.IsNullOrEmpty(JingXiaoShang))
            {
                //if (JingXiaoShang == "湖南金力源")
                //{
                //    sqlcommonFiled = " JingXiaoShang in('湖南金力源','湖南金土地')";
                //}
                //else
                //{
                    sqlcommonFiled = QueryOperatorsEnumHelper.ConvertCommonOperator("JingXiaoShang", QueryOperatorsEnum.等于, JingXiaoShang);
                //}



                if (!string.IsNullOrEmpty(wheresql))
                {
                    wheresql += " and " + sqlcommonFiled;
                }
                else
                {
                    wheresql = sqlcommonFiled;
                }

            }



            if (!string.IsNullOrEmpty(HeTongHao))
            {
                string DOWNSQL = QueryOperatorsEnumHelper.ConvertCommonOperator("HeTongHao", QueryOperatorsEnum.等于, HeTongHao);
                if (!string.IsNullOrEmpty(wheresql))
                {
                    wheresql += " and " + DOWNSQL;
                }
                else
                {
                    wheresql = DOWNSQL;
                }
            }


            if (!string.IsNullOrEmpty(XiaoShouShiJianFrom))
            {
                string DOWNSQL = QueryOperatorsEnumHelper.ConvertCommonOperator("XiaoShouShiJian", QueryOperatorsEnum.范围, XiaoShouShiJianFrom + "|" + XiaoShouShiJianTo);
                if (!string.IsNullOrEmpty(wheresql))
                {
                    wheresql += " and " + DOWNSQL;
                }
                else
                {
                    wheresql = DOWNSQL;
                }
            }



            string dutySQL = "";
            if (!string.IsNullOrEmpty(JiHao))
            {
                dutySQL = QueryOperatorsEnumHelper.ConvertCommonOperator("JiHao", QueryOperatorsEnum.包含, JiHao);
                if (!string.IsNullOrEmpty(wheresql))
                {
                    wheresql += " and " + dutySQL;
                }
                else
                {
                    wheresql = dutySQL;
                }
            }

            if (JiQiState != "-")
            {
                string DOWNSQL = QueryOperatorsEnumHelper.ConvertCommonOperator("JiQiState", QueryOperatorsEnum.等于, JiQiState);
                if (!string.IsNullOrEmpty(wheresql))
                {
                    wheresql += " and " + DOWNSQL;
                }
                else
                {
                    wheresql = DOWNSQL;
                }
            }

            //2014.2.19 加入从分期汇总报表过来的处理
            if (IsShowFenQi == 1)
            {
                XiaoShouLeiXing = "全款,2个月帐期,3个月帐期,4个月及以上帐期";
            }

            if (XiaoShouLeiXing != "")
            {
                string[] xslxs = XiaoShouLeiXing.Split(new char[] { ',' });
                string xslxsql = " XiaoShouLeiXing not in (''";
                foreach (string itm in xslxs)
                {
                    if (!string.IsNullOrEmpty(itm))
                    {
                        xslxsql += string.Format(",'{0}'", itm);
                    }
                }
                xslxsql += ")";

                if (!string.IsNullOrEmpty(wheresql))
                {
                    wheresql += " and " + xslxsql;
                }
                else
                {
                    wheresql = xslxsql;
                }
            }

            return wheresql;
        }

        protected override string SQLSort
        {
            get
            {
                return " XiaoShouShiJian desc ";
            }
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            DataTable allAgentList = FTradeReportController.GetALLJinXiaoShang(GetSQLWhere());
            //DataRow dr=  allAgentList.NewRow();
            //dr["JingXiaoShang"] = "湖南金土地";
           // allAgentList.Rows.Add(dr);
            int TotalCount = 0;
            DataTableCollection dts = FTradeReceivablesController.GetPagination(GetSQLWhere(), 1, 9999999, SQLSort, ref TotalCount);

            //帐龄信息加工
            DataTable DaoQiRidt = FTradeReportController.GetMinMaxDaoQiRi(GetSQLWhere());
            DateTime minDaoQiRi;
            DateTime maxDaoQiRi;
            if (DaoQiRidt.Rows.Count <= 0)
            {
                minDaoQiRi = DateTime.Now;
                maxDaoQiRi = DateTime.Now;
            }
            else
            {
                minDaoQiRi = DaoQiRidt.Rows[0]["minDaoQiRi"] is DBNull ? DateTime.Now : Convert.ToDateTime(DaoQiRidt.Rows[0]["minDaoQiRi"]);
                maxDaoQiRi = DaoQiRidt.Rows[0]["maxDaoQiRi"] is DBNull ? DateTime.Now : Convert.ToDateTime(DaoQiRidt.Rows[0]["maxDaoQiRi"]);
            }

            //获取帐龄明细
            DataTable AgeDetailJinEDT = FTradeReportController.Report1GetAgeDetailJinE(GetSQLWhere());
            
            //初始化
            InitializeWorkbook();
            ISheet sheet1 = hssfworkbook.GetSheet(SheetName);
            //1.写入帐龄标题列
            int titleRowColumnIndex=17;
            for (DateTime dtTemp = minDaoQiRi; dtTemp < maxDaoQiRi.AddMonths(1); dtTemp = dtTemp.AddMonths(1))
            {
                string startDT = dtTemp.ToString("yyyy-MM-01");
                string endDT = Convert.ToDateTime(dtTemp.AddMonths(1).ToString("yyyy-MM-01")).AddDays(-1).ToString("yyyy-MM-dd");
              
                sheet1.GetRow(1).CreateCell(titleRowColumnIndex).SetCellValue(dtTemp.ToString("yyyy-MM"));
                titleRowColumnIndex++;
            }
            //2.按经销商各个写入
            int writeStartRow = 2;
            int writeStartCell = 17;
            foreach (DataRow agentRow in allAgentList.Rows)
            {
                string agentName = agentRow["JingXiaoShang"].ToString();
                DataTable agentDetil_temp = dts[0].Select("JingXiaoShang='" + agentName + "'").CopyToDataTable();

                agentDetil_temp.DefaultView.Sort = "ZDXiaoShouState ASC";
                agentDetil_temp = agentDetil_temp.DefaultView.ToTable();
                agentDetil_temp.DefaultView.Sort = " XiaoShouLeiXing ASC";


                DataTable agentDetil = agentDetil_temp.DefaultView.ToTable();


                DataTable agentAgeDetail=AgeDetailJinEDT.Select("JingXiaoShang='" + agentName + "'").CopyToDataTable();
                writeStartRow=WriteAgentDetail(agentName, agentDetil,AgeDetailJinEDT, sheet1, writeStartRow, writeStartCell,minDaoQiRi,maxDaoQiRi);
                //break;
            }
            //写入总计 行
            //小计格式设置
            IFont font = hssfworkbook.CreateFont();
            ICellStyle style1 = hssfworkbook.CreateCellStyle();
            font.Boldweight = short.MaxValue;

            style1.FillForegroundColor = HSSFColor.WHITE.index;
            style1.FillPattern = FillPatternType.FINE_DOTS;
            style1.FillBackgroundColor = HSSFColor.GREY_50_PERCENT.index;
            style1.SetFont(font);
            writeStartRow=writeStartRow+1;
            HSSFRow writerowSum = (HSSFRow)sheet1.CreateRow(writeStartRow);
            writerowSum.CreateCell(0).SetCellValue("合计");
            writerowSum.CreateCell(6).SetCellFormula(string.Format("sum(G{0}:G{1})/2", 3, writeStartRow-1));
            writerowSum.CreateCell(7).SetCellFormula(string.Format("sum(H{0}:H{1})/2", 3, writeStartRow - 1));
            writerowSum.CreateCell(9).SetCellFormula(string.Format("sum(J{0}:J{1})/2", 3, writeStartRow - 1));
            writerowSum.CreateCell(10).SetCellFormula(string.Format("sum(K{0}:K{1})/2", 3, writeStartRow - 1));

            writerowSum.CreateCell(16).SetCellFormula(string.Format("sum(Q{0}:Q{1})/2", 3, writeStartRow - 1));

            for (int icell = 0; icell < writerowSum.LastCellNum; icell++)
            {
                try
                {
                    writerowSum.GetCell(icell).CellStyle = style1;
                }
                catch
                {
                    writerowSum.CreateCell(icell).CellStyle = style1;
                }
            }
            
            for (DateTime dtTemp = minDaoQiRi; dtTemp < maxDaoQiRi.AddMonths(1); dtTemp = dtTemp.AddMonths(1))
            {
                writerowSum.CreateCell(writeStartCell).SetCellFormula(string.Format("sum({2}{0}:{2}{1})/2", 3, writeStartRow - 1, GetCellName(writeStartCell)));
                writerowSum.GetCell(writeStartCell).CellStyle = style1;
                writeStartCell++;
            }
            //行高
            writerowSum.Height = 15 * 20;
            //写入
            WriteToFile();
        }

        protected int WriteAgentDetail(string agentName, DataTable detail, DataTable agentAgeDetail, ISheet sheet1, int startRowIndex, int startCellIndex, DateTime minDaoQiRi, DateTime maxDaoQiRi)
        {
            int sumstartRow = startRowIndex;
            int writerowindex = startRowIndex;
            int writecellindex = startCellIndex;
            
            foreach (DataRow dr in detail.Rows)
            {
                HSSFRow writerow = (HSSFRow)sheet1.CreateRow(writerowindex);
                writerow.CreateCell(0).SetCellValue(dr["XiaoShouShiJian"].ToString());
                writerow.CreateCell(1).SetCellValue(dr["YingYeJingLi"].ToString());
                writerow.CreateCell(2).SetCellValue(dr["JingXiaoShang"].ToString());
                writerow.CreateCell(3).SetCellValue(dr["HeTongHao"].ToString());
                writerow.CreateCell(4).SetCellValue(dr["XingHao"].ToString());
                writerow.CreateCell(5).SetCellValue(dr["JiHao"].ToString());
                writerow.CreateCell(6).SetCellValue(Convert.ToDouble( dr["Price"]));
                writerow.CreateCell(7).SetCellValue(Convert.ToDouble(dr["HeTongJinE"]));
                //writerow.CreateCell(8).SetCellValue(Convert.ToDouble(dr["HeTongJinE"]));
                writerow.CreateCell(9).SetCellValue(Convert.ToDouble(dr["YiShouKuanXiang"]));
                writerow.CreateCell(10).SetCellValue(Convert.ToDouble(dr["WeiShouKuanXiang"]));
                writerow.CreateCell(11).SetCellValue(dr["DaoQiRi"].ToString());
                writerow.CreateCell(12).SetCellValue(dr["FuKuanTiaoKuan"].ToString());
                writerow.CreateCell(13).SetCellValue(dr["ZDXiaoShouState"].ToString());
                writerow.CreateCell(14).SetCellValue(dr["ZDXiaoShouShiJian"].ToString());
                writerow.CreateCell(15).SetCellValue(dr["XiaoShouLeiXing"].ToString());
                writerow.CreateCell(16).SetCellValue(Convert.ToDouble(dr["YuQiKuanXiang"]));
                //写入帐龄
                //获取列值
                writecellindex = startCellIndex;
                for (DateTime dtTemp = minDaoQiRi; dtTemp < maxDaoQiRi.AddMonths(1); dtTemp = dtTemp.AddMonths(1))
                {
                    string startDT = dtTemp.ToString("yyyy-MM-01");
                    string endDT = Convert.ToDateTime(dtTemp.AddMonths(1).ToString("yyyy-MM-01")).AddDays(-1).ToString("yyyy-MM-dd");
                    object JinE = agentAgeDetail.Compute("sum(JinE)", string.Format(" FTradeID={0} and DaoQiRi >= '{1}' and DaoQiRi<='{2}'", dr["FTradeID"], startDT, endDT));
                    
                    writerow.CreateCell(writecellindex).SetCellValue(Convert.ToDouble(JinE is DBNull?0:JinE));
                    writecellindex++;
                }
                writerowindex++;
            }
            //写入小计
            //小计格式设置
            IFont font = hssfworkbook.CreateFont();
            ICellStyle style1 = hssfworkbook.CreateCellStyle();
            font.Boldweight = short.MaxValue;
            
            style1.FillForegroundColor = HSSFColor.WHITE.index;
            style1.FillPattern = FillPatternType.FINE_DOTS;
            style1.FillBackgroundColor = HSSFColor.GREY_50_PERCENT.index;
            style1.SetFont(font);
            //cell1.CellStyle = style1;

            HSSFRow writerowSum = (HSSFRow)sheet1.CreateRow(writerowindex);
            writerowSum.CreateCell(0).SetCellValue("计");
            writerowSum.CreateCell(2).SetCellValue(agentName);    
            writerowSum.CreateCell(6).SetCellFormula(string.Format("sum(G{0}:G{1})",startRowIndex+1,writerowindex));
            writerowSum.CreateCell(7).SetCellFormula(string.Format("sum(H{0}:H{1})", startRowIndex + 1, writerowindex));
            writerowSum.CreateCell(9).SetCellFormula(string.Format("sum(J{0}:J{1})", startRowIndex + 1, writerowindex));
            writerowSum.CreateCell(10).SetCellFormula(string.Format("sum(K{0}:K{1})", startRowIndex + 1, writerowindex));
            writerowSum.CreateCell(16).SetCellFormula(string.Format("sum(Q{0}:Q{1})", startRowIndex + 1, writerowindex));
            //writerowSum.CreateCell(writerowSum.LastCellNum - 1).SetCellFormula(string.Format("sum(BN{0}:BN{1})", startRowIndex, writerowindex));
            for (int icell = 0; icell < startCellIndex; icell++)
            {
                try
                {
                    writerowSum.GetCell(icell).CellStyle = style1;
                }
                catch
                {
                    writerowSum.CreateCell(icell).CellStyle = style1;
                }
            }
            writecellindex = startCellIndex;
            for (DateTime dtTemp = minDaoQiRi; dtTemp < maxDaoQiRi.AddMonths(1); dtTemp = dtTemp.AddMonths(1))
            {
               

                writerowSum.CreateCell(writecellindex).SetCellFormula(string.Format("sum({2}{0}:{2}{1})", startRowIndex + 1, writerowindex, GetCellName(writecellindex)));
                writerowSum.GetCell(writecellindex).CellStyle = style1;

                writecellindex++;
            }
            //行高
            writerowSum.Height = 15 * 20;

            writerowindex++;
            return writerowindex;
        }

       

        protected override string GetXlsFileName(string oldFileName)
        {
            //return base.GetXlsFileName(oldFileName);

            return string.Format("{0}{1}.xls","应收帐款汇总表",DateTime.Now.ToString("yyyy-MM-dd")).Trim();
        }
    }
}